<?php

use Illuminate\Database\Capsule\Manager as DB;

class ExcelController extends AbstractController
{
    public function export( $data , $filename = "simport" )
    {

        require "../application/library/Phpexcel/PHPExcel.php";
        ini_set('max_execution_time', '0');

        $filename=str_replace('.xls', '', $filename).'.xls';
        $phpexcel = new \PHPExcel();
        $phpexcel->getProperties()
            ->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        $phpexcel->getActiveSheet()->fromArray($data);
        $phpexcel->getActiveSheet()->setTitle('Sheet1');
        $phpexcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$filename");
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0

        $objwriter = \PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
        $objwriter->save('php://output');
    }
    // 默认Action
    public function indexAction()
    {

        $where['an_questionnaireid'] = $this->getRequest()->getQuery('dim_questionid',6);

        $arr = DB::table('answer')
            ->leftJoin("questionnaire","qu_id","=","an_questionnaireid")
            ->where($where)
            ->get();
        $data = array();
        $title = array();

        //查出该问卷所有题目
        $whereTOP['top_status'] = 1 ;
        $whereTOP['top_questionnaireid'] = $where['an_questionnaireid'];
        $topic_list = DB::table('topic')
            ->where($whereTOP)
            ->orderBy('top_section','asc')
            ->orderBy('top_order','desc')
            ->lists("top_id");

        //查出该问卷有几个大维度
        $dim_list = DB::table('dimension')
            ->where("dim_questionid","=",$where['an_questionnaireid'])
            ->where('dim_father','=',0)
            ->where("dim_status",'=',1)
            ->get();

        //查出有几个小维度
        $second_list = DB::table('dimension')
            ->where("dim_questionid","=",$where['an_questionnaireid'])
            ->where('dim_father','!=',0)
            ->where("dim_status",'=',1)
            ->get();

        //获取标准分对比的json
        $score = object_array( json_decode(file_get_contents("score.json")));


        //组装头部标题
        //基本信息标题
        $title[] = "问卷名称";
        $title[] = "答题编号";
        $title[] = "用户openid";
        $title[] = "用户昵称";
        $title[] = "答题时间";
        //题目标题
        foreach( $topic_list as $key => $val )
        {
            $n = $key+1;
            $title [] = "题目{$n}答案";
        }

        foreach( $arr as $key => $val )
        {
            //组装基本信息
            $data[$key]['an_questionnaireid'] = $val['qu_title'];
            $data[$key]['an_number'] = "1000".$val['an_number'];
            $data[$key]['an_openid'] = $val['an_openid'];
            $data[$key]['an_nickname'] = $val['an_nickname'];
            $data[$key]['an_addtime'] = date( "Y-m-d H:i:s" , $val['an_addtime'] );

            //组装答题选项
            //查出该问卷所有答题选项
            $option_list = DB::table("answer_option")
                ->where("aop_answerid",'=',$val['an_id'])
                ->get();
            //如果是多选题，将多选题用逗号隔开
            $opt_list = array();
            $aop_topicid = 0;
            $aop_k = 0;
            foreach( $option_list as $k => $v )
            {
                if( $v['aop_topicid'] != $aop_topicid )
                {
                    $opt_list[$k] = $v;
                    $aop_topicid = $v['aop_topicid'];
                    $aop_k = $k;
                } else {
                    $opt_list[$aop_k]['aop_position'] .= ",".$v['aop_position'];
                }
            }
//            var_dump($opt_list);exit;
            foreach( $topic_list as $key1 => $val1 )
            {
                //循环所有子选项，找出与题目对应的，输出到对应的表格位置上
                $top = '0';
                foreach( $opt_list as $k1 => $v1 )
                {
                    if( $val1 == $v1['aop_topicid'] )
                    {
                        $top = $v1['aop_position'];
                    }
                }
                $data[$key][] = "$top" ;
            }

            //组装所属维度
            //大维度分数
            foreach( $dim_list as $k => $v )
            {
                //一级维度的计算
                //如果是原始总分   = 1
                //如果是原始平均分 = 2
                DB::connection()->enableQueryLog(); // 开启查询日志
                $res = "0";
                if( $v['dim_scoring'] == 1 )
                {
                    $whereDIM['adi_dimension'] = $v['dim_id'];//答题所属大维度等于该大维度的
                    $whereDIM['adi_answerid'] = $val['an_id']; //属于该答题的
                    $res = DB::table('answer_dimension')->where($whereDIM)->sum("adi_grade");
                    $res = round($res,2);
                }
                else if( $v['dim_scoring'] == 2 )
                {
                    $whereDIM['adi_dimension'] = $v['dim_id'];//答题所属大维度等于该大维度的
                    $whereDIM['adi_answerid'] = $val['an_id']; //属于该答题的
                    $res = DB::table('answer_dimension')->where($whereDIM)->avg("adi_grade");
                    $res = round($res,2);
                }
                else if( $v['dim_scoring'] == 3 )
                {
                    //如果是百分比
                    $whereDIM['adi_dimension'] = $v['dim_id'];//答题所属小维度等于该小维度的
                    $whereDIM['adi_answerid'] = $val['an_id']; //属于该答题的
                    $res = DB::table('answer_dimension')->where($whereDIM)->avg("adi_grade");

                    $res = ( $res - $v['dim_mean'] ) / $v['dim_deviation'];
                    $res = round( $res ,2);

                    if( $res < -3 )
                    {
                        $res = -3;
                    }
                    if( $res > 3 )
                    {
                        $res = 3;
                    }
                    $res = sprintf("%01.2f",$res);
                    $res = $score["$res"] * 1;

                }

                $data[$key][] = "$res";

                //循环输出大维度标题
                if( $key == 0 )
                {
                    $title [] = $v['dim_name'] . "得分";
                }

            }

            foreach( $second_list as $k => $v )
            {
                $res = "0";

                if( $v['dim_scoring'] == 1 )
                {
                    //如果是原始总分
                    $whereSEC['adi_second_dimsion'] = $v['dim_id'];//答题所属小维度等于该小维度的
                    $whereSEC['adi_answerid'] = $val['an_id']; //属于该答题的
                    $res = DB::table('answer_dimension')->where($whereSEC)->sum("adi_grade");
                }
                else if( $v['dim_scoring'] == 2 )
                {
                    //如果是原始平均分
                    $whereSEC['adi_second_dimsion'] = $v['dim_id'];//答题所属小维度等于该小维度的
                    $whereSEC['adi_answerid'] = $val['an_id']; //属于该答题的
                    $res = DB::table('answer_dimension')->where($whereSEC)->avg("adi_grade");
                    $res = round($res ,2);
                }
                else if( $v['dim_scoring'] == 3 )
                {
                    //如果是百分比
                    $whereSEC['adi_second_dimsion'] = $v['dim_id'];//答题所属小维度等于该小维度的
                    $whereSEC['adi_answerid'] = $val['an_id']; //属于该答题的
                    $res = DB::table('answer_dimension')->where($whereSEC)->avg("adi_grade");

                    $res = ( $res - $v['dim_mean'] ) / $v['dim_deviation'];
                    $res = round( $res ,2);

                    if( $res < -3 )
                    {
                        $res = -3;
                    }

                    if( $res > 3 )
                    {
                        $res = 3;
                    }

                    $res = sprintf("%01.2f",$res);
                    $res = $score["$res"] * 1;

                }

                $data[$key][] = "$res";

                //输出小维度得分
                if( $key == 0 )
                {
                    $title [] = $v['dim_name'] . "得分";
                }
            }

        }
        $qu_name = DB::table("questionnaire")->where("qu_id","=",$this->getRequest()->getQuery("dim_questionid",6))->pluck("qu_title")."测试结果".date("Ymd",time());

        array_unshift( $data , $title );
        $this->export($data,$qu_name);

    }

    //导出某个问卷的题目维度对照表
    public function dimexportAction()
    {
        $data = array();
        $title = array();
        $title[] = "问题编号";
        $title[] = "问题id";
        $title[] = "题目类型";
        $title[] = "一级维度";
        $title[] = "二级维度";
        $title[] = "选项1得分";
        $title[] = "选项2得分";
        $title[] = "选项3得分";
        $title[] = "选项4得分";
        $title[] = "选项5得分";
        $title[] = "选项6得分";
        $title[] = "选项7得分";
        $title[] = "选项8得分";
        $title[] = "选项9得分";
        $title[] = "选项10得分";

        //查询问卷信息，用来命名文件名
        $qu_name = DB::table("questionnaire")->where("qu_id","=",$this->getRequest()->getQuery("qu_id",6))->pluck("qu_title")."基础配置".date("Ymd",time());

        //查询题目列表
        $where['top_questionnaireid'] = $this->getRequest()->getQuery('qu_id',6);
        $where['top_status'] = 1;

        $topic_list = DB::table('topic')
            ->where($where)
            ->leftJoin("dimension","dim_id","=","top_dimension")
            ->orderBy("top_order","desc")
            ->get();

        foreach( $topic_list as $key => $val )
        {
            $data[$key]['id'] = $key + 1;
            $data[$key]['top_id'] = $val['top_id'];
            switch($val['top_type'])
            {
                case "1":
                    $data[$key]['top_type'] = "单选题";
                    break;
                case "2":
                    $data[$key]['top_type'] = "多选题";
                    break;
                case "3":
                    $data[$key]['top_type'] = "填空题";
                    break;
                case "4":
                    $data[$key]['top_type'] = "量表题";
                    break;
                case "5":
                    $data[$key]['top_type'] = "跳转题";
                    break;
                default:
                    $data[$key]['top_type'] = "这条题目没有类型";
            }

//            $data[$key]['top_type'] = $val['top_type'];
            $data[$key]['top_dimension'] = $val['dim_name'];
            $data[$key]['top_second_dimension'] = DB::table("dimension")->where("dim_id","=",$val['top_second_dimension'])->pluck("dim_name");

            //循环查出选项得分
            //获取题目所有选项
            $option_list = DB::table('topic_option')
                ->where('opt_topicid','=',$val['top_id'])
                ->orderBy('opt_id','asc')
                ->get();

            //如果是量表题，算出量表的输出数组
            if( $val['top_type'] == 4 )
            {
                //如果间隔为0，默认改为1
                if( $val['top_interval'] == 0 )
                {
                    $val['top_interval'] = 1;
                }
                if( $val['top_reverse'] == 1 )
                {
                    //如果没有反向
                    for ( $i = $val['top_maxnumber'] ; $i >= $val['top_minnumber'] ; $i = $i - $val['top_interval']  )
                    {
                        $data[$key][] = "$i";
                    }

                } else {
                    //如果是反向
                    for ( $i = $val['top_minnumber'] ; $i <= $val['top_maxnumber'] ; $i = $i + $val['top_interval']  )
                    {
                        $data[$key][] = "$i";
                    }

                }

            } else if( $val['top_type'] == 1 || $val['top_type'] == 2 ) {
                //如果题目是单选或多选题
                foreach( $option_list as $k => $v )
                {
                    $grade = $v['opt_grade'];
                    $data[$key][] = "$grade";
                }

            }
        }

        array_unshift( $data , $title );
        $this->export( $data , $qu_name);
    }

}
